package com.eric.husky.dao;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

import com.eric.husky.bean.Task;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface TaskDao extends JpaRepository<Task, Integer>{

//    @Query(value="",nativeQuery=true)
//    List<Map<String,Object>> getHomeList(String pid);

//    @Query(value="SELECT b.username,c.name eidname,a.is_black isBlack,a.is_conn_db isConnDb,a.is_node_db isNodeDb,a.is_order_db isOrderDb," +
//            "a.id,a.name,a.position,a.sn,DATE_FORMAT(a.createtime,'%Y-%m-%d %H:%i:%S') createtime,DATE_FORMAT(a.updatetime,'%Y-%m-%d %H:%i:%S') updatetime,a.describes " +
//            "FROM (select * from model_device where product_id =:productid) a\n" +
//            " LEFT JOIN model_user b ON a.user_id = b.id\n" +
//            " LEFT JOIN model_eid c ON a.eid = c.id\n" +
//            " WHERE a.name LIKE  %:keywords%  OR a.describes LIKE  %:keywords% "
//            ,countQuery="SELECT count(1)  FROM (select * from model_device where product_id =:productid) a " +
//            " WHERE a.name LIKE  %:keywords%  OR a.describes LIKE  %:keywords% "
//            ,nativeQuery=true)//原生sql语句SELECT 获取root组所有分组
//    Page<Map<String , Object>> getTaskPage2(Pageable pageable, @Param("keywords") String keywords, @Param("productid") String productid);

//    @Query(value="select * from (SELECT a.title,a.number,count(c.taskid) ywc,a.money,b.realname,b.picurl FROM task a\n" +
//            "LEFT JOIN user b ON a.userid = b.userid\n" +
//            "LEFT JOIN record c ON a.taskid = c.taskid\n" +
//            "WHERE a.title LIKE  %:keywords%  OR b.realname LIKE  %:keywords% " +
//            "GROUP BY a.taskid) a"
//            ,countQuery="select count(1) from task a\n" +
//            "LEFT JOIN user b ON\ta.userid = b.userid\n" +
//            "LEFT JOIN record c ON a.taskid = c.taskid " +
//            "WHERE a.title LIKE  %:keywords%  OR b.realname LIKE  %:keywords% "
//            ,nativeQuery=true)//原生sql语句SELECT 获取root组所有分组
//    Page<Map<String , Object>> getTaskPage(Pageable pageable, @Param("keywords") String keywords);

    @Query(value="select * from (SELECT a.title,a.number,count(c.taskid) ywc,a.money,b.realname,b.picurl FROM task a\n" +
            "LEFT JOIN user b ON a.userid = b.userid\n" +
            "LEFT JOIN record c ON a.taskid = c.taskid\n" +
            "GROUP BY a.taskid) a"
            ,countQuery="select count(1) from task a\n" +
            "LEFT JOIN user b ON\ta.userid = b.userid\n" +
            "LEFT JOIN record c ON a.taskid = c.taskid "
            ,nativeQuery=true)//原生sql语句SELECT 获取root组所有分组
    Page<Map<String , Object>> getTaskPage(Pageable pageable, @Param("keywords") String keywords);
}
